package com.moleq.network;

import java.security.interfaces.DSAKey;
import java.text.DateFormat;
import java.text.NumberFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import com.moleq.db.DatabaseHelper;
import com.moleq.db.DatabaseSchema;
import com.moleq.models.FreeFuncRpt;

import android.R.integer;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;


public class ReportCreator {
	private static final String ITEM_SALES = "Item/Sales";
	private static final String NET_SALES = "Net Sales";
	private static final String CASH_IN_DRAWER = "Cash In Drawer";
	private static final String CREDIT_IN_DRAWER = "Credit In Drawer";
	private static final String DEBIT_IN_DRAWER = "Debit In Drawer";
	private static final String CHECK_IN_DRAWER = "Check In Drawer";
	private static final String FOODSTAMP_IN_DRAWER = "FoodStamp In Drawer";
	private static final String EBT_IN_DRAWER = "EBT In Drawer";
	private static final String GIFT_CARD_IN_DRAWER = "Gift Card In Drawer";
	private static final String REFUND_SALES = "Refund Sales";
	private static final String TRANSACTION_COUNT = "Transaction Count";
	private static final String AVERAGE = "Average";
	private static final String DISCOUNT = "Discount";
	private static final String COUPON = "Coupon";
	private static final String ROUNDING = "Rounding";
	private static final String VDR_COUPON = "Vendor Coupon";
	private static final String TAXABLE_1 = "Taxable 1";
	private static final String TAX_1 = "Tax 1";
	private static final String TAX_EXEMPT_1 = "Tax Exempt 1";
	private static final String TAXABLE_2 = "Taxable 2";
	private static final String TAX_2 = "Tax 2";
	private static final String TAX_EXEMPT_2 = "Tax Exempt 2";
	private static final String TAXABLE_3 = "Taxable 3";
	private static final String TAX_3 = "Tax 3";
	private static final String TAX_EXEMPT_3 = "Tax Exempt 3";
	private static final String TAXABLE_4 = "Taxable 4";
	private static final String TAX_4 = "Tax 4";
	private static final String TAX_EXEMPT_4 = "Tax Exempt 4";
	private static final String TAXABLE_5 = "Taxable 5";
	private static final String TAX_5 = "Tax 5";
	private static final String TAX_EXEMPT_5 = "Tax Exempt 5";
	private static final String TAXABLE_6 = "Taxable 6";
	private static final String TAX_6 = "Tax 6";
	private static final String TAX_EXEMPT_6 = "Tax Exempt 6";

	private DatabaseHelper DBHelper;

	public ReportCreator() {
	}
	
	public ReportCreator(Context context){
		this.DBHelper = new DatabaseHelper(context);
	}

	public String generateReport(String... args) {
		String strRpt = "";
		String rptName = args[0];
		String station = "";
		String fromDate = args[1];
		String toDate = args[2];
		// String rptName = Constant.RPT_FIXED_TOTAL;
		// String fromDate = "20120922";
		// String toDate = "20121022";
		if (rptName.equals(Constant.RPT_DEPARTMENT)) {
			strRpt = getDeptSalesRptsByDate(fromDate, toDate);
		} else if (rptName.equals(Constant.RPT_FIXED_TOTAL)) {
			strRpt = getFixedTotalRepByDate(fromDate, toDate);
		} else if (rptName.equals(Constant.RPT_FUNCTION)) {
			strRpt = getFreeFuncRptByDate(fromDate,toDate);
		} else if (rptName.equals(Constant.RPT_GROUP)) {
			strRpt = getGroupSalesRptByDate(fromDate, toDate);
		} else if (rptName.equals(Constant.RPT_PLU_MULTI_TARE)) {
			strRpt = getMultiTareSalesRptsByDate(fromDate, toDate);
		} else if (rptName.equals(Constant.RPT_SCANNING_ITEM)) {
			strRpt = getPLUSalesRptByDate(fromDate, toDate);
		}

		// strRpt = "New Report (" + rptName + ", from date:" + fromDate +
		// " to date:" + toDate + ")";

		return strRpt;
	}

	public String getFixedTotalRepByDate(String fromDate, String toDate) {
		// String sql = "select * from t_fixed_total_rpt where ft_date = '" +
		// Date + "' order by ft_id";
		String result = "";
		String sql = "select ft_id,ft_store_id,ft_station,ft_date,sum(ft_gs_qty) as ft_gs_qty,sum(ft_gs_amt) as ft_gs_amt,"
				+ "sum(ft_ns_qty) as ft_ns_qty,sum(ft_ns_amt) as ft_ns_amt,sum(ft_cash_id) as ft_cash_id,"
				+ "sum(ft_credit_id) as ft_credit_id,sum(ft_debit_id) as ft_debit_id,sum(ft_check_id) as ft_check_id,"
				+ "sum(ft_foodstamp_id) as ft_foodstamp_id,sum(ft_ebt_id) as ft_ebt_id,sum(ft_gc_id) as ft_gc_id,"
				+ "sum(ft_refund_qty) as ft_refund_qty,sum(ft_refund_amt) as ft_refund_amt,sum(ft_trn_count) as ft_trn_count,"
				+ "sum(ft_average) as ft_average,sum(ft_discount) as ft_discount,sum(ft_coupon) as ft_coupon,"
				+ "sum(ft_taxable_1) as ft_taxable_1,sum(ft_tax_1) as ft_tax_1,sum(ft_tax_exem_1) as ft_tax_exem_1,"
				+ "sum(ft_taxable_2) as ft_taxable_2,sum(ft_tax_2) as ft_tax_2,sum(ft_tax_exem_2) as ft_tax_exem_2,"
				+ "sum(ft_taxable_3) as ft_taxable_3,sum(ft_tax_3) as ft_tax_3,sum(ft_tax_exem_3) as ft_tax_exem_3,"
				+ "sum(ft_taxable_4) as ft_taxable_4,sum(ft_tax_4) as ft_tax_4,sum(ft_tax_exem_4) as ft_tax_exem_4,"
				+ "sum(ft_taxable_5) as ft_taxable_5,sum(ft_tax_5) as ft_tax_5,sum(ft_tax_exem_5) as ft_tax_exem_5,"
				+ "sum(ft_taxable_6) as ft_taxable_6,sum(ft_tax_6) as ft_tax_6,sum(ft_tax_exem_6) as ft_tax_exem_6,"
				+ "ft_user_id,sum(ft_rounding) as ft_rounding,sum(ft_vdr_coupon) as ft_vdr_coupon from t_fixed_total_rpt where ft_date >= '"
				+ fromDate
				+ "' and ft_date <= '"
				+ toDate
				+ "' group by ft_date order by ft_id";
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		Cursor cursor = db.rawQuery(sql, null);
		if (cursor == null)
			return "";
		while (cursor.moveToNext()) {
			int ft_id = cursor.getInt(cursor
					.getColumnIndex(DatabaseSchema.FixedTotalRpt.ID));
			int ft_store_id = cursor.getInt(cursor
					.getColumnIndex(DatabaseSchema.FixedTotalRpt.STORE_ID));
			int ft_station = cursor.getInt(cursor
					.getColumnIndex(DatabaseSchema.FixedTotalRpt.STATION));
			String ft_date = cursor.getString(cursor
					.getColumnIndex(DatabaseSchema.FixedTotalRpt.DATE));
			int ft_gs_qty = cursor.getInt(cursor
					.getColumnIndex(DatabaseSchema.FixedTotalRpt.GS_QTY));
			double ft_gs_amt = cursor.getDouble(cursor
					.getColumnIndex(DatabaseSchema.FixedTotalRpt.GS_AMT));
			int ft_ns_qty = cursor.getInt(cursor
					.getColumnIndex(DatabaseSchema.FixedTotalRpt.NS_QTY));
			double ft_ns_amt = cursor.getDouble(cursor
					.getColumnIndex(DatabaseSchema.FixedTotalRpt.NS_AMT));
			double ft_cash_id = cursor.getDouble(cursor
					.getColumnIndex(DatabaseSchema.FixedTotalRpt.CASH_ID));
			double ft_credit_id = cursor.getDouble(cursor
					.getColumnIndex(DatabaseSchema.FixedTotalRpt.CREDIT_ID));

			double ft_debit_id = cursor.getDouble(cursor
					.getColumnIndex(DatabaseSchema.FixedTotalRpt.DEBIT_ID));
			double ft_check_id = cursor.getDouble(cursor
					.getColumnIndex(DatabaseSchema.FixedTotalRpt.CHECK_ID));
			double ft_foodstamp_id = cursor.getDouble(cursor
					.getColumnIndex(DatabaseSchema.FixedTotalRpt.FOODSTAMP_ID));
			double ft_ebt_id = cursor.getDouble(cursor
					.getColumnIndex(DatabaseSchema.FixedTotalRpt.EBT_ID));
			double ft_gc_id = cursor.getDouble(cursor
					.getColumnIndex(DatabaseSchema.FixedTotalRpt.GC_ID));
			int ft_refund_qty = cursor.getInt(cursor
					.getColumnIndex(DatabaseSchema.FixedTotalRpt.REFUND_QTY));
			double ft_refund_amt = cursor.getDouble(cursor
					.getColumnIndex(DatabaseSchema.FixedTotalRpt.REFUND_AMT));
			int ft_trn_count = cursor.getInt(cursor
					.getColumnIndex(DatabaseSchema.FixedTotalRpt.TRN_COUNT));
			double ft_average = cursor.getDouble(cursor
					.getColumnIndex(DatabaseSchema.FixedTotalRpt.AVERAGE));
			double ft_discount = cursor.getDouble(cursor
					.getColumnIndex(DatabaseSchema.FixedTotalRpt.DISCOUNT));

			double ft_coupon = cursor.getDouble(cursor
					.getColumnIndex(DatabaseSchema.FixedTotalRpt.COUPON));
			double ft_taxable_1 = cursor.getDouble(cursor
					.getColumnIndex(DatabaseSchema.FixedTotalRpt.TAXABLE_1));
			double ft_tax_1 = cursor.getDouble(cursor
					.getColumnIndex(DatabaseSchema.FixedTotalRpt.TAX_1));
			double ft_tax_exem_1 = cursor.getDouble(cursor
					.getColumnIndex(DatabaseSchema.FixedTotalRpt.TAX_EXEM_1));
			double ft_taxable_2 = cursor.getDouble(cursor
					.getColumnIndex(DatabaseSchema.FixedTotalRpt.TAXABLE_2));
			double ft_tax_2 = cursor.getDouble(cursor
					.getColumnIndex(DatabaseSchema.FixedTotalRpt.TAX_2));
			double ft_tax_exem_2 = cursor.getDouble(cursor
					.getColumnIndex(DatabaseSchema.FixedTotalRpt.TAX_EXEM_2));
			double ft_taxable_3 = cursor.getDouble(cursor
					.getColumnIndex(DatabaseSchema.FixedTotalRpt.TAXABLE_3));
			double ft_tax_3 = cursor.getDouble(cursor
					.getColumnIndex(DatabaseSchema.FixedTotalRpt.TAX_3));
			double ft_tax_exem_3 = cursor.getDouble(cursor
					.getColumnIndex(DatabaseSchema.FixedTotalRpt.TAX_EXEM_3));

			double ft_taxable_4 = cursor.getDouble(cursor
					.getColumnIndex(DatabaseSchema.FixedTotalRpt.TAXABLE_4));
			double ft_tax_4 = cursor.getDouble(cursor
					.getColumnIndex(DatabaseSchema.FixedTotalRpt.TAX_4));
			double ft_tax_exem_4 = cursor.getDouble(cursor
					.getColumnIndex(DatabaseSchema.FixedTotalRpt.TAX_EXEM_4));
			double ft_taxable_5 = cursor.getDouble(cursor
					.getColumnIndex(DatabaseSchema.FixedTotalRpt.TAXABLE_5));
			double ft_tax_5 = cursor.getDouble(cursor
					.getColumnIndex(DatabaseSchema.FixedTotalRpt.TAX_5));
			double ft_tax_exem_5 = cursor.getDouble(cursor
					.getColumnIndex(DatabaseSchema.FixedTotalRpt.TAX_EXEM_5));
			double ft_taxable_6 = cursor.getDouble(cursor
					.getColumnIndex(DatabaseSchema.FixedTotalRpt.TAXABLE_6));
			double ft_tax_6 = cursor.getDouble(cursor
					.getColumnIndex(DatabaseSchema.FixedTotalRpt.TAX_6));
			double ft_tax_exem_6 = cursor.getDouble(cursor
					.getColumnIndex(DatabaseSchema.FixedTotalRpt.TAX_EXEM_6));
			String ft_user_id = cursor.getString(cursor
					.getColumnIndex(DatabaseSchema.FixedTotalRpt.FT_USER_ID));
			double ft_rounding = cursor.getDouble(cursor
					.getColumnIndex(DatabaseSchema.FixedTotalRpt.FT_ROUNDING));
			double ft_vdr_coupon = cursor.getDouble(cursor
					.getColumnIndex(DatabaseSchema.FixedTotalRpt.FT_VDR_COUPON));
			

			result = "";
			if (ft_gs_amt != 0 || ft_gs_qty != 0) {
				result += "|(" + "01," + ITEM_SALES + "," + ft_gs_qty + ","
						+ ft_gs_amt + ")";
			}
			if (ft_ns_amt != 0 || ft_ns_qty != 0) {
				result += "|(" + "02," + NET_SALES + "," + ft_ns_qty + ","
						+ ft_ns_amt + ")";
			}
			if (ft_cash_id != 0) {
				result += "|(" + "03," + CASH_IN_DRAWER + ", ," + ft_cash_id
						+ ")";
			}
			if (ft_credit_id != 0) {
				result += "|(" + "04," + CREDIT_IN_DRAWER + ", ,"
						+ ft_credit_id + ")";
			}
			if (ft_debit_id != 0) {
				result += "|(" + "05," + DEBIT_IN_DRAWER + ", ," + ft_debit_id
						+ ")";
			}
			if (ft_check_id != 0) {
				result += "|(" + "06," + CHECK_IN_DRAWER + ", ," + ft_check_id
						+ ")";
			}
			if (ft_foodstamp_id != 0) {
				result += "|(" + "07," + FOODSTAMP_IN_DRAWER + ", ,"
						+ ft_foodstamp_id + ")";
			}
			if (ft_ebt_id != 0) {
				result += "|(" + "08," + EBT_IN_DRAWER + ", ," + ft_ebt_id
						+ ")";
			}
			if (ft_gc_id != 0) {
				result += "|(" + "09," + GIFT_CARD_IN_DRAWER + ", ," + ft_gc_id
						+ ")";
			}
			if (ft_refund_amt != 0 || ft_refund_qty != 0) {
				result += "|(" + "10," + REFUND_SALES + "," + ft_refund_qty
						+ "," + ft_refund_amt + ")";
			}
			if (ft_trn_count != 0) {
				result += "|(" + "11," + TRANSACTION_COUNT + "," + ft_trn_count
						+ "," + " " + ")";
			}
			if (ft_average != 0) {
				result += "|(" + "12," + AVERAGE + ", ," + ft_average + ")";
			}
			if (ft_discount != 0) {
				result += "|(" + "13," + DISCOUNT + ", ," + ft_discount + ")";
			}
			if (ft_coupon != 0) {
				result += "|(" + "14," + COUPON + ", ," + ft_coupon + ")";
			}
			
			if (ft_rounding != 0) {
				ft_rounding *= -1;
				result += "|(" + "15," + ROUNDING + ", ," + ft_rounding + ")";
			}
			
			if (ft_vdr_coupon != 0) {
				result += "|(" + "16," + VDR_COUPON + ", ," + ft_vdr_coupon + ")";
			}
			
			if (ft_taxable_1 != 0) {
				result += "|(" + "17," + TAXABLE_1 + ", ," + ft_taxable_1 + ")";
			}
			if (ft_tax_1 != 0) {
				result += "|(" + "18," + TAX_1 + ", ," + ft_tax_1 + ")";
			}
			if (ft_tax_exem_1 != 0) {
				result += "|(" + "19," + TAX_EXEMPT_1 + ", ," + ft_tax_exem_1
						+ ")";
			}

			if (ft_taxable_2 != 0) {
				result += "|(" + "20," + TAXABLE_2 + ", ," + ft_taxable_2 + ")";
			}
			if (ft_tax_2 != 0) {
				result += "|(" + "21," + TAX_2 + ", ," + ft_tax_2 + ")";
			}
			if (ft_tax_exem_2 != 0) {
				result += "|(" + "22," + TAX_EXEMPT_2 + ", ," + ft_tax_exem_2
						+ ")";
			}

			if (ft_taxable_3 != 0) {
				result += "|(" + "23," + TAXABLE_3 + ", ," + ft_taxable_3 + ")";
			}
			if (ft_tax_3 != 0) {
				result += "|(" + "24," + TAX_3 + ", ," + ft_tax_3 + ")";
			}
			if (ft_tax_exem_3 != 0) {
				result += "|(" + "25," + TAX_EXEMPT_3 + ", ," + ft_tax_exem_3
						+ ")";
			}

			if (ft_taxable_4 != 0) {
				result += "|(" + "26," + TAXABLE_4 + ", ," + ft_taxable_4 + ")";
			}
			if (ft_tax_4 != 0) {
				result += "|(" + "27," + TAX_4 + ", ," + ft_tax_4 + ")";
			}
			if (ft_tax_exem_4 != 0) {
				result += "|(" + "28," + TAX_EXEMPT_4 + ", ," + ft_tax_exem_4
						+ ")";
			}

			if (ft_taxable_5 != 0) {
				result += "|(" + "29," + TAXABLE_5 + ", ," + ft_taxable_5 + ")";
			}
			if (ft_tax_5 != 0) {
				result += "|(" + "30," + TAX_5 + ", ," + ft_tax_5 + ")";
			}
			if (ft_tax_exem_5 != 0) {
				result += "|(" + "31," + TAX_EXEMPT_5 + ", ," + ft_tax_exem_5
						+ ")";
			}

			if (ft_taxable_6 != 0) {
				result += "|(" + "32," + TAXABLE_6 + ", ," + ft_taxable_6 + ")";
			}
			if (ft_tax_6 != 0) {
				result += "|(" + "33," + TAX_6 + ", ," + ft_tax_6 + ")";
			}
			if (ft_tax_exem_6 != 0) {
				result += "|(" + "34," + TAX_EXEMPT_6 + ", ," + ft_tax_exem_6
						+ ")";
			}
		}
		cursor.close();
		if (result.length() > 0) {
			return Constant.RPT_FIXED_TOTAL + result;
		} else {
			return result;
		}
	}

	public String getDeptSalesRptsByDate(String fromDate, String toDate) {
		String sql = "select ds_id,ds_store_id,ds_station,ds_date,ds_dept,sum(ds_qty),sum(ds_amt),create_datetime,update_datetime,ds_type from t_dept_sales_rpt where ds_date >= '"
				+ fromDate
				+ "' and ds_date <= '"
				+ toDate
				+ "' group by ds_date,ds_dept,ds_store_id,ds_station order by ds_dept";
		String result = "";
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		Cursor cursor = db.rawQuery(sql, null);
		if (cursor == null)
			return result;
		while (cursor.moveToNext()) {
			String sDepName = getDeptName(String.valueOf(cursor.getInt(4)));
			if (sDepName.trim().equals("") || sDepName == null) {
				sDepName = "";
			}
			int iDepId = cursor.getInt(4);
			int iDepQty = cursor.getInt(5);
			double dDepAmt = cursor.getDouble(6);
			if (iDepQty != 0 || dDepAmt != 0) {
				result += "|(" + iDepId + "," + CheckSign(CheckNull(sDepName))
						+ "," + iDepQty + "," + dDepAmt + ")";
			}
		}
		cursor.close();
		if (result.length() > 0) {
			return Constant.RPT_DEPARTMENT + result;
		} else {
			return result;
		}
	}

	public String getDeptName(String DepId) {
		String sql = "select dep_name from t_department_setting where F03 = "
				+ DepId;
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		Cursor cursor = db.rawQuery(sql, null);
		if (cursor == null)
			return "";
		if (cursor.moveToNext()) {
			String name = cursor.getString(0);
			cursor.close();
			return name;
		}
		return "";
	}

	public String getFreeFuncRptByDate(String fromDate, String toDate) {
		String sql = "select *,sum(ff_qty) from t_free_func_rpt where ff_date >= '"
				+ fromDate
				+ "' and ff_date <= '"
				+ toDate
				+ "' group by ff_func_id order by ff_func_id";
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		Cursor cursor = db.rawQuery(sql, null);
		String result = "";
		if (cursor == null)
			return result;
		while (cursor.moveToNext()) {
			int iFunId = cursor.getInt(4);
			String sFunName = getFunName(String.valueOf(iFunId));
			int iFunQty = cursor.getInt(10);
			double dFunAmt = cursor.getDouble(6);
			if (iFunId == FreeFuncRpt.FF_ERROR_CORRECT || iFunId == FreeFuncRpt.FF_VOID 
					|| iFunId == FreeFuncRpt.FF_TARE || iFunId == FreeFuncRpt.FF_PRICE_CHANGE
					|| iFunId == FreeFuncRpt.FF_TAX_EXEMPT || iFunId == FreeFuncRpt.FF_NO_SALE
					|| iFunId == FreeFuncRpt.SHIFT_TAX || iFunId == FreeFuncRpt.SHIFT_FOODSTAMP
					|| iFunId == FreeFuncRpt.FF_SIGN_OUT) {
				result += "|(" + iFunId + "," + CheckSign(CheckNull(sFunName))
						+ "," + iFunQty + ", )";
			}else {
				result += "|(" + iFunId + "," + CheckSign(CheckNull(sFunName))
						+ "," + iFunQty + "," + dFunAmt + ")";
			}
//			if (iFunQty > 0) {
//				result += "|(" + iFunId + "," + CheckSign(CheckNull(sFunName))
//						+ "," + iFunQty + "," + dFunAmt + ")";
//			}
		}
		cursor.close();
		if (result.length() > 0) {
			return Constant.RPT_FUNCTION + result;
		} else {
			return result;
		}
	}

	public String getFunName(String FunId) {
		String sql = "select ff_name from t_free_functions where ff_id = "
				+ FunId;
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		Cursor cursor = db.rawQuery(sql, null);
		if (cursor == null)
			return "";
		if (cursor.moveToNext()) {
			String name = cursor.getString(0);
			cursor.close();
			return name;
		}
		return "";
	}

	public String getGroupSalesRptByDate(String fromDate, String toDate) {
		String sql = "select * from t_group_sales_rpt where gs_date >= '"
				+ fromDate + "' and gs_date <= '" + toDate
				+ "' order by gs_group";
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		Cursor cursor = db.rawQuery(sql, null);
		String result = "";
		if (cursor == null)
			return result;
		while (cursor.moveToNext()) {
			int iGroupId = cursor.getInt(4);
			String sGroupName = getDeptGroupName(String.valueOf(iGroupId));
			int iGrpQty = cursor.getInt(5);
			double dGrpAmt = cursor.getDouble(6);
			if (iGrpQty != 0 || dGrpAmt != 0) {
				result += "|(" + iGroupId + ","
						+ CheckSign(CheckNull(sGroupName)) + "," + iGrpQty
						+ "," + dGrpAmt + ")";
			}
		}
		cursor.close();
		if (result.length() > 0) {
			return Constant.RPT_GROUP + result;
		} else {
			return result;
		}
	}

	public String getDeptGroupName(String GrpId) {
		String sql = "select dg_name from t_dept_group where dg_no = " + GrpId;
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		Cursor cursor = db.rawQuery(sql, null);
		if (cursor == null)
			return "";
		if (cursor.moveToNext()) {
			String name = cursor.getString(0);
			cursor.close();
			return name;
		}
		return "";
	}

	public String getMultiTareSalesRptsByDate(String fromDate, String toDate) {
		String sql = "select mt_tare_no,mt_qty,F01,F02 from t_multi_tare_sales_rpt,t_item_setting where mt_date = '"
				+ fromDate
				+ "' and mt_date <= '"
				+ toDate
				+ "' and F01 = mt_upc_plu order by mt_id";
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		Cursor cursor = db.rawQuery(sql, null);
		String result = "";
		if (cursor == null)
			return result;
		String upcString = "";
		while (cursor.moveToNext()) {
			int iTareId = cursor.getInt(0);
			String sTareName = getTareName(String.valueOf(iTareId));
			int iTareQty = cursor.getInt(1);
			String upc = cursor.getString(2) + "  " + cursor.getString(3);
			String upc2 = cursor.getString(2);
			// if (iTareQty > 0) {
			// result += "|(" + CheckSign(CheckNull(upc)) + "," + iTareId + ","
			// + CheckSign(CheckNull(iTareName)) + "," + iTareQty+")";
			// }
			if (iTareQty != 0) {
				if (!upcString.equals(upc2)) {
					upcString = upc2;
					if (upcString.length() > 45) {
						upcString = upcString.substring(0, 45);
					}
					result += "|(" + upc2 + ","
							+ CheckSign(CheckNull(upcString)) + ", , )";
				}
				result += "|(" + upc2 + "-" + iTareId + ","
						+ CheckSign(CheckNull("  " + sTareName)) + ", ,"
						+ iTareQty + " )";
			}
		}
		cursor.close();
		if (result.length() > 0) {
			return Constant.RPT_PLU_MULTI_TARE + result;
		} else {
			return result;
		}
	}

	public String getTareName(String TareId) {
		String sql = "select ta_desc from t_tare_setting where ta_no = "
				+ TareId;
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		Cursor cursor = db.rawQuery(sql, null);
		if (cursor == null)
			return "";
		if (cursor.moveToNext()) {
			String name = cursor.getString(0);
			cursor.close();
			return name;
		}
		return "";
	}

	public String getPLUSalesRptByDate(String fromDate, String toDate) {
		String sql = "select us_id,us_store_id,us_station,us_date,us_upc_plu,sum(us_qty),us_weight,us_wt_unit,sum(us_amt),create_datetime,update_datetime,us_type,us_sub_dep from t_plu_sales_rpt where us_date = '"
				+ fromDate
				+ "' and us_date <= '"
				+ toDate
				+ "' group by us_store_id,us_station,us_date,us_upc_plu,us_type order by us_id,us_upc_plu";
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		Cursor cursor = db.rawQuery(sql, null);
		String result = "";
		if (cursor == null)
			return result;
		while (cursor.moveToNext()) {
			String sPLU = cursor.getString(4);
			String itemName = "";
			if (sPLU.length() < 12) {
				itemName = getItemDiscount(sPLU);
			} else {
				itemName = getItemName(sPLU);
			}
			double dWeight = cursor.getDouble(6);
			int iQty = cursor.getInt(5);
			double dAmt = cursor.getDouble(8);
			String sType = cursor.getString(11);
			String unit = cursor.getString(7);
			if (iQty != 0 || dWeight != 0 || dAmt != 0) {
				if (sType.equals("1020")) {
					result += "|(" + CheckSign(CheckNull(sPLU + "-D")) + ","
							+ CheckSign(CheckNull("    Discount")) + ","
							+ String.valueOf(iQty) + "," + dAmt + ")";
				} else {
					result += "|(" + CheckSign(CheckNull(sPLU)) + ","
							+ CheckSign(CheckNull(sPLU)) + ","
							+ String.valueOf(iQty) + "," + dAmt + ")";
					if (dWeight > 0) {
						result += "|(" + CheckSign(CheckNull(sPLU + "-A")) + ","
								+ CheckSign(CheckNull(itemName));
						result += "," + dWeight + "," + unit + ")";
					} else {
						result += "|(" + CheckSign(CheckNull(sPLU + "-A"))
								+ "," + CheckSign(CheckNull(itemName));
						result += ", , )";
					}

				}

			}
		}
		cursor.close();
		if (result.length() > 0) {
			return Constant.RPT_SCANNING_ITEM + result;
		} else {
			return result;
		}
	}

	public String getItemDiscount(String PLU) {
		String sql = "select ds_desc from t_discount_setting where ds_no = "
				+ PLU;
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		Cursor cursor = db.rawQuery(sql, null);
		if (cursor == null)
			return "";
		if (cursor.moveToNext()) {
			String name = cursor.getString(0);
			cursor.close();
			return name;
		}
		return "--Discount Not Found--";
	}

	public String getItemName(String PLU) {
		String sql = "select F02 from t_item_setting where F01 = '" + PLU + "'";
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		Cursor cursor = db.rawQuery(sql, null);
		if (cursor == null)
			return "";
		if (cursor.moveToNext()) {
			String name = cursor.getString(0);
			cursor.close();
			return name;
		}
		return "--Name Not Found--";
	}

	public String CheckSign(String str) {
		if (str == null)
			return null;
		String data = str.substring(1, (str.length() - 1));
		String result = "";
		for (int i = 0; i < data.length(); i++) {
			String charString = String.valueOf(data.charAt(i));
			if (charString.equals("'")) {
				result += charString + "'";
			} else {
				result += charString;
			}
		}

		return "'" + result + "'";
	}

	public String CheckNull(String value) {
		if (value == null) {
			return null;
		} else {
			return "'" + value + "'";
		}
	}
}
